﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using C.Unite;
using C.Entities;
using ChanExt;
using MySoft.Data;
using System.Text;
using LitJson;
using NPOI.DDF;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.POIFS;
using NPOI.SS.UserModel;
using NPOI.Util;
using System.IO;

namespace ArtLand.OA.Project.HR
{
    public partial class HR_InsuranceSearch : CommonPageBase
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                DataBaseHelper.BindYear(Drp_Year);
                DataBaseHelper.BindMonth(Drp_Mouth);
                Drp_Year.Items.Insert(0, new ListItem("年份", "0"));
                Drp_Mouth.Items.Insert(0, new ListItem("月份", "0"));
                BindGrid(GetWhere());
            }
        }


        #region BindGrid
        private void BindGrid(WhereClip wc)
        {
            Grid1.SelectedRowIndex = -1;
            // 1.设置总项数
            Grid1.RecordCount = TbsDbSession.Default.From<HR_Insurance>().Where(wc).Count();

            List<HR_Insurance> items = TbsDbSession.Default.From<HR_Insurance>().Where(wc).OrderBy(HR_Insurance._.ID.Desc).Page(Grid1.PageSize, (Grid1.PageIndex + 1)).ToList();

            // 3.绑定到Grid
            Grid1.DataSource = items;
            Grid1.DataBind();
        }



        protected void Grid1_PageIndexChange(object sender, ChanExt.GridPageEventArgs e)
        {
            Grid1.PageIndex = e.NewPageIndex;
            BindGrid(GetWhere());
        }


        #endregion

        #region Events

        public WhereClip GetWhere()
        {
            WhereClip wc = WhereClip.All;
            if (Drp_Year.SelectedValue != "0")
                wc = wc && HR_Insurance._.PayYear == int.Parse(Drp_Year.SelectedValue);
            if (Drp_Mouth.SelectedValue != "0")
                wc = wc && HR_Insurance._.PayMonth == int.Parse(Drp_Mouth.SelectedValue);
            // wc = wc && HR_Insurance._.EmpNo == Request["empid"];
            if (!String.IsNullOrEmpty(ttbxMyBox2.Text))
            {
                wc = wc && (HR_Insurance._.EmpNo.Contain(ttbxMyBox2.Text) || HR_Insurance._.EmpNo.Contain(ttbxMyBox2.Text));
            }
            return wc;
        }

        protected void SearchLoad(object sender, EventArgs e)
        {
            BindGrid(GetWhere());
        }

        // 点击 TwinTriggerBox 的搜索按钮
        protected void ttbxMyBox2_Trigger2Click(object sender, EventArgs e)
        {
            if (!String.IsNullOrEmpty(ttbxMyBox2.Text))
            {
                // 执行搜索动作
                BindGrid(GetWhere());
                ttbxMyBox2.ShowTrigger1 = true;
            }
            else
            {
                Alert.ShowInTop("请输入你要搜索的关键词！");
            }
        }

        // 点击 TwinTriggerBox 的取消按钮
        protected void ttbxMyBox2_Trigger1Click(object sender, EventArgs e)
        {
            // 执行清空动作

            ttbxMyBox2.Text = "";
            ttbxMyBox2.ShowTrigger1 = false;
            BindGrid(GetWhere());
        }


        //protected void btnDelete_Click(object sender, EventArgs e)
        //{
        //    int returnvalue = 0;
        //    bool bl = true;
        //    foreach (int row in Grid1.SelectedRowIndexArray)
        //    {
        //        int id=int.Parse(Grid1.DataKeys[row][0].ToString());
        //        int c=TbsDbSession.Default.From<Sys_Modules>().Where(Sys_Modules._.Topmid==id).Count();
        //        if (c == 0)
        //        {
        //            returnvalue += TbsDbSession.Default.Delete<Sys_Modules>(Sys_Modules._.Mid == id);
        //        }
        //        else
        //        {
        //            bl = false;
        //        }

        //    }
        //    if (returnvalue > 0)
        //    {
        //        if (bl == false)
        //        {
        //            Alert.ShowInTop("删除成功！但有些模块还存在子模块！");
        //            BindGrid();
        //        }
        //        else
        //        {
        //            Alert.ShowInTop("删除成功！");
        //            BindGrid();
        //        }
        //    }

        //}


        #endregion
        protected void Grid1_RowDataBound(object sender, ChanExt.GridRowEventArgs e)
        {
            string empno = e.Values[1].ToString();
            string m = e.Values[12].ToString();
            HR_EMP hr = TbsDbSession.Default.From<HR_EMP>().Where(HR_EMP._.EMPLID == empno).ToFirst();
            if (hr != null)
                e.Values[2] = hr.EMNAME;
            e.Values[0] = e.Values[0] + "年" + m + "月";
        }

        protected void Grid1_RowCommand(object sender, ChanExt.GridCommandEventArgs e)
        {
            int id = int.Parse(Grid1.DataKeys[e.RowIndex][0].ToString());
            if (e.CommandName == "Del")
            {
                TbsDbSession.Default.Delete<HR_Insurance>(HR_Insurance._.ID == id);
                Alert.ShowInTop("删除成功！");
                BindGrid(GetWhere());
            }

        }


        #region 导出到excel
        protected void ExportData(string TempExcPath, string ExportName, List<HR_Insurance> list)
        {
            try
            {
                FileStream file = new FileStream(HttpContext.Current.Server.MapPath(TempExcPath), FileMode.Open, FileAccess.Read);
                MemoryStream ms = new MemoryStream();//定义一个数据流
                IWorkbook hssfworkbook = new HSSFWorkbook(file);//创建一个工作溥

                #region 设置单元格的样式Style8
                ICellStyle style8 = hssfworkbook.CreateCellStyle(); //创建一个样式
                IFont font = hssfworkbook.CreateFont(); //创建一个文档字体
                font.FontHeightInPoints = 9;    //设置字体大小
                style8.SetFont(font);           //给样式设定字体
                style8.Alignment = HorizontalAlignment.CENTER;//水平对齐
                style8.VerticalAlignment = VerticalAlignment.CENTER;//垂直对齐
                style8.WrapText = false;//自动换行
                style8.BorderBottom = CellBorderType.THIN;//下边框样式
                style8.BorderLeft = CellBorderType.THIN;//左边框样式
                style8.BorderRight = CellBorderType.THIN;//右边框样式
                style8.BorderTop = CellBorderType.THIN;//上边框样式
                #region 设置背景色
                style8.FillForegroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
                style8.FillPattern = FillPatternType.SQUARES;
                style8.FillBackgroundColor = HSSFColor.LIGHT_CORNFLOWER_BLUE.index;
                #endregion
                #endregion

                #region 设置单元格的样式STyle9
                ICellStyle style9 = hssfworkbook.CreateCellStyle(); //创建一个样式
                IFont font1 = hssfworkbook.CreateFont(); //创建一个文档字体
                font1.FontHeightInPoints = 9;    //设置字体大小
                style9.SetFont(font1);           //给样式设定字体
                style9.Alignment = HorizontalAlignment.CENTER;//水平对齐
                style9.VerticalAlignment = VerticalAlignment.CENTER;//垂直对齐
                style9.WrapText = true;//自动换行
                style9.BorderBottom = CellBorderType.THIN;//下边框样式
                style9.BorderLeft = CellBorderType.THIN;//左边框样式
                style9.BorderRight = CellBorderType.THIN;//右边框样式
                style9.BorderTop = CellBorderType.THIN;//上边框样式
                #region 设置背景色
                style9.FillForegroundColor = HSSFColor.SEA_GREEN.index;
                style9.FillPattern = FillPatternType.SQUARES;
                style9.FillBackgroundColor = HSSFColor.SEA_GREEN.index;
                #endregion
                #endregion

                ISheet sheet1 = hssfworkbook.GetSheet("Sheet1");//取得工作表为1

                ICellStyle cs = null;

                cs = style8;//设置样式
                for (int i = 0; i < list.Count; i++)
                {
                    HR_Insurance tp = list[i];
                    HR_EMP he = TbsDbSession.Default.From<HR_EMP>().Where(HR_EMP._.EMPLID == tp.EmpNo).ToFirst();
                    string incompanyTime = "";
                    HR_CONTRACT cont = TbsDbSession.Default.From<HR_CONTRACT>().Where(HR_CONTRACT._.EMPLID == he.EMPLID).ToFirst();
                    if (cont != null)
                        incompanyTime = cont.ENTRYTIME.Value.ToString("yyyy-MM-dd");

                    sheet1.CreateRow(i + 1).CreateCell(0).CellStyle = cs;//第一行首先创建
                    sheet1.GetRow(i + 1).GetCell(0).SetCellValue(tp.PayYear + "." + tp.PayMonth);

                    sheet1.GetRow(i + 1).CreateCell(1).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(1).SetCellValue(he.COMPAY);

                    sheet1.GetRow(i + 1).CreateCell(2).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(2).SetCellValue(he.EMNAME);

                    sheet1.GetRow(i + 1).CreateCell(3).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(3).SetCellValue(he.IDCard);

                    sheet1.GetRow(i + 1).CreateCell(4).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(4).SetCellValue(tp.PayType);

                    sheet1.GetRow(i + 1).CreateCell(5).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(5).SetCellValue(tp.PayStuts);

                    sheet1.GetRow(i + 1).CreateCell(6).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(6).SetCellValue(incompanyTime);

                    sheet1.GetRow(i + 1).CreateCell(7).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(7).SetCellValue(tp.PayStartMonth);

                    sheet1.GetRow(i + 1).CreateCell(8).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(8).SetCellValue(tp.SocialBase.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(9).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(9).SetCellValue(tp.ProvBase.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(10).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(10).SetCellValue(tp.NursPerson.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(11).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(11).SetCellValue(tp.MedicalPerson.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(12).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(12).SetCellValue(tp.UnemployePerson.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(13).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(13).SetCellValue(tp.PersonTotal.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(14).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(14).SetCellValue(tp.NursCompany.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(15).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(15).SetCellValue(tp.MedicalCompany.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(16).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(16).SetCellValue(tp.UnemployeCompany.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(17).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(17).SetCellValue(tp.BirthCompany.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(18).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(18).SetCellValue(tp.InjuryCompany.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(19).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(19).SetCellValue(tp.CompanyTotal.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(20).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(20).SetCellValue(tp.SocialTotal.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(21).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(21).SetCellValue(tp.ProvPerson.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(22).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(22).SetCellValue(tp.ProvCompany.Value.ToString());


                    sheet1.GetRow(i + 1).CreateCell(23).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(23).SetCellValue(tp.ProvTotal.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(24).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(24).SetCellValue(tp.CompanyAmount.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(25).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(25).SetCellValue(tp.PersonAmount.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(26).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(26).SetCellValue(tp.Amounts.Value.ToString());

                    sheet1.GetRow(i + 1).CreateCell(27).CellStyle = cs;
                    sheet1.GetRow(i + 1).GetCell(27).SetCellValue(tp.Memo);
                }

                sheet1.ForceFormulaRecalculation = true;
                hssfworkbook.SetSheetName(0, ExportName);
                hssfworkbook.Write(ms);
                Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + ExportName));
                Response.BinaryWrite(ms.ToArray());
                hssfworkbook = null;
                ms.Close();
                ms.Dispose();
                Response.End();
            }
            catch (Exception ex)
            {
                Alert.Show("导出数据出现错误:" + ex.Message);
            }
        }
        protected void btn_Export_Click(object sender, EventArgs e)
        {
            string str = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss:ffff").Replace(" ", "").Replace("-", "").Replace(":", "");
            ExportData("~/HR/2014.xls", str + ".xls", TbsDbSession.Default.From<HR_Insurance>().Where(GetWhere()).ToList());
        }
        #endregion


    }
}